﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace DAO
{
    public class dbConnection
    {
        SqlConnection con;
        public dbConnection()
        {
            con = new SqlConnection(Properties.Resources.Local);
        }
        private SqlConnection openConnection()
        {
            if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
            {
                con.Open();
            }
            return con;
        }
        public DataTable executeQuery(String query,params SqlParameter[] sqlParameter)
        {
            SqlDataAdapter adapter = null;
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = query;
                myCommand.Parameters.AddRange(sqlParameter);
                adapter= new SqlDataAdapter(myCommand);
                adapter.Fill(dataTable);               
            }
            catch (SqlException e)
            {
               
               MessageBox.Show("Error - Connection.executeSelectQuery - Query: " + query + " \nException: " + e.StackTrace.ToString());
               return null;
            }
            finally
            {
                con.Close();
            }
            return dataTable;
        }
        public DataTable executeQuery(String query)
        {
            SqlDataAdapter adapter = null;
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = query;
                adapter= new SqlDataAdapter(myCommand);
                adapter.Fill(dataTable);               
            }
            catch (SqlException e)
            {
               
               MessageBox.Show("Error - Connection.executeSelectQuery - Query: " + query + " \nException: " + e.StackTrace.ToString());
               return null;
            }
            finally
            {
                con.Close();
            }
            return dataTable;
        }
        
        public bool executeNonQuery(String query,params SqlParameter[] sqlParameter)
        {
            int i = 0;
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = query;
                myCommand.Parameters.AddRange(sqlParameter);
                i=(int)myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Error - Connection.executeSelectQuery - Query: " + query + " \nException: " + e.Message);
                return false;
            }
            finally
            {
                con.Close();
            }
            if (i > 0)
                return true;
            else
                return false;
        }
        public Object excuteScalar(String query, params SqlParameter[] sqlParameter)
        {

            Object obj;
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = query;
                myCommand.Parameters.AddRange(sqlParameter);
                obj = myCommand.ExecuteScalar();
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeSelectQuery - Query: " + query + " \nException: " + e.StackTrace.ToString());
                return null;
            }
            finally
            {
                con.Close();
            }
            return obj;
        }
    }
}
